﻿using System;
using System.Collections.Generic;
using System.Text;
using BBMS.BLL.DataContract;
using BBMS.DAL.IDAL;
using MySql.Data.MySqlClient;
using System.Data;
using BBMS.DAL.MySQLHelper;

namespace BBMS.DAL.MySQL
{
    public class Bank : IBank
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region SQL

        private const string INSERT_BANK = "INSERT INTO bank(code, provider, beneficiary, beneficiaryshort, accountno, accounttype, dep) VALUES (@code, @provider, @beneficiary, @beneficiaryshort, @accountno, @accounttype, @dep) ON DUPLICATE KEY UPDATE code=@code, provider@=provider, beneficiary=@beneficiary, beneficiaryshort=@beneficiaryshort, accountno=@accountno, accounttype=@accounttype, dep=@dep)";

        private const string DELETE_BANK_BYPROVIDER = "DELETE FROM bank WHERE provider=@provider";

        private const string GET_BANK_BYPROVIDER = "SELECT * FROM bank WHERE provider=@provider";

        private const string GET_BANK_BYCODE = "SELECT * FROM bank WHERE code=@code";

        #endregion

        #region Params

        private const string PARM_CODE = "@CODE";
        private const string PARM_PROVIDER = "@PROVIDER";
        private const string PARM_BENEFICIARY = "@BENEFICIARY";
        private const string PARM_BENEFICIARY_SHORT = "@BENEFICIARYSHORT";
        private const string PARM_ACCOUNTNO = "@ACCOUNTNO";
        private const string PARM_ACCOUNTTYPE = "@ACCOUNTTYPE";
        private const string PARM_DEP = "@DEP";

        #endregion
        
        #region IBank Members


        public void insertBank(BankData item, string provider)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                    new MySqlParameter( PARM_CODE, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_PROVIDER, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_BENEFICIARY, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_BENEFICIARY_SHORT, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_ACCOUNTNO, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_ACCOUNTTYPE, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_DEP, MySqlDbType.VarChar)
                };

                parms[0].Value = item.Code;
                parms[1].Value = provider;
                parms[2].Value = item.Beneficiary;
                parms[3].Value = item.BeneficiaryShort;
                parms[4].Value = item.AccountNo;
                parms[5].Value = item.AccountType;
                parms[6].Value = item.Dep;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_BANK, parms);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public void deleteBank(string code)
        {
            throw new NotImplementedException();
        }

        public void deleteBankByProvider(string provider)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_PROVIDER, MySqlDbType.VarChar);
                parm1.Value = provider;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, DELETE_BANK_BYPROVIDER, parm1);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public List<BankData> getAllBank()
        {
            throw new NotImplementedException();
        }

        public List<BankData> getBankByProvider(string provider)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_PROVIDER, MySqlDbType.VarChar);
                parm1.Value = provider;

                List<BankData> list = new List<BankData>();

                MySqlDataReader rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANK_BYPROVIDER, parm1);

                while (rdr.Read())
                {
                    list.Add(new BankData(rdr[PARM_CODE].ToString(),
                                            rdr[PARM_BENEFICIARY].ToString(),
                                            rdr[PARM_BENEFICIARY_SHORT].ToString(),
                                            rdr[PARM_ACCOUNTNO].ToString(),
                                            rdr[PARM_ACCOUNTTYPE].ToString(),
                                            rdr[PARM_DEP].ToString()
                                           ));
                }

                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public BankData getBankByCode(string code)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CODE, MySqlDbType.VarChar);
                parm1.Value = code;
                BankData item = new BankData();

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANK_BYCODE, parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    item.Code = rdr[PARM_CODE].ToString();
                    item.Beneficiary = rdr[PARM_BENEFICIARY].ToString();
                    item.BeneficiaryShort = rdr[PARM_BENEFICIARY_SHORT].ToString();
                    item.AccountNo = rdr[PARM_ACCOUNTNO].ToString();
                    item.AccountType = rdr[PARM_ACCOUNTTYPE].ToString();
                    item.Dep = rdr[PARM_DEP].ToString();
                }
                return item;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public BankData getBankByName(string name)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
